﻿using System.Data;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data.OracleClient;
using System;
using System.Drawing;
using System.Text;
using System.Globalization;

namespace BSC_SERVICES.Code
{
    public class Connect_DataBase
    {
        #region khai bao bien
        string _connectstring = ConfigurationManager.AppSettings["ConnectString"].ToString();
        OracleDataAdapter _da = new OracleDataAdapter();
        OracleCommand _cmd = new OracleCommand();
        OracleConnection _con = new OracleConnection();
        DataTable _table = new DataTable();
        CL_Ultility _cl = new CL_Ultility();
        Common _cm = new Common();
        #endregion

        #region ham connect
        public void OpenConnect()
        {
            if (_con.State != ConnectionState.Open)
            {
                _con.ConnectionString = _connectstring;
                _con.Open();    
            }
        }

        public void CloseConnect()
        {
            _con.Close();
        }

        private string RunSql_ByParameter(string sql, List<string> _paras, List<object> _values, List<string> _datatype, string _action)
        {
            OpenConnect();
            _cmd = new OracleCommand(sql);
            for (int i = 0; i < _paras.Count; i++)
            {
                switch (_datatype[i])
                {
                    case "B":
                        _cmd.Parameters.Add(_paras[i], _cl.RowType(_datatype[i])).Value = _values[i];
                        break;
                    case "D":
                        if (_values[i].ToString().Length == 0)
                            _cmd.Parameters.Add(_paras[i], _cl.RowType(_datatype[i])).Value = OracleDateTime.Null;
                        else
                            _cmd.Parameters.Add(_paras[i], _cl.RowType(_datatype[i])).Value = DateTime.ParseExact(_values[i].ToString(), "dd/MM/yyyy", CultureInfo.InvariantCulture);
                        break;
                    default:
                        _cmd.Parameters.Add(_paras[i], _cl.RowType(_datatype[i]), 2000).Value = _values[i];
                        break;
                }
            }
            _cmd.Connection = _con;
            try
            {
                _cmd.ExecuteNonQuery();
                CloseConnect();
            }
            catch (System.Exception ex)
            {
                CloseConnect();
                _cl.UPDATE_LOG_FILE("ERR", null, _action, "RunSql_ByParameter", ex.Message);
                return ex.Message;
            }
            if (_action == "Add")
            {
                return CL_Ultility.messeseboxThemmoiThanhCong;
            }
            if (_action == "Edit")
            {
                return CL_Ultility.messeseboxSuaThanhCong;
            }
            return "Error";
        }

        private string RunSql_ByParameter_ReturnID(string sql, List<string> _paras, List<object> _values, List<string> _datatype, string _action)
        {
            OpenConnect();
            _cmd = new OracleCommand(sql);
            for (int i = 0; i < _paras.Count; i++)
            {
                switch (_datatype[i])
                {
                    case "B":
                        _cmd.Parameters.Add(_paras[i], _cl.RowType(_datatype[i])).Value = _values[i];
                        break;
                    case "D":
                        if (_values[i].ToString().Length == 0)
                            _cmd.Parameters.Add(_paras[i], _cl.RowType(_datatype[i])).Value = OracleDateTime.Null;
                        else
                            _cmd.Parameters.Add(_paras[i], _cl.RowType(_datatype[i])).Value = DateTime.ParseExact(_values[i].ToString(), "dd/MM/yyyy", CultureInfo.InvariantCulture);
                        break;
                    default:
                        _cmd.Parameters.Add(_paras[i], _cl.RowType(_datatype[i]), 2000).Value = _values[i];
                        break;
                }
            }
            _cmd.Parameters.Add("myOutputParameter", OracleType.Int16).Direction = ParameterDirection.ReturnValue;
            //_cmd.Parameters.Add(new OracleParameter("myOutputParameter", OracleType.Int16), ParameterDirection.ReturnValue);
            _cmd.Connection = _con;
            try
            {
                _cmd.ExecuteNonQuery();
                CloseConnect();
            }
            catch (System.Exception ex)
            {
                CloseConnect();
                _cl.UPDATE_LOG_FILE("ERR", null, _action, "RunSql_ByParameter_ReturnID", ex.Message);
                return ex.Message;
            }
            if (_action == "Add")
            {
                return _cmd.Parameters["myOutputParameter"].Value.ToString();
            }
            if (_action == "Edit")
            {
                return _cmd.Parameters["myOutputParameter"].Value.ToString();
            }
            return "Error";
        }

        public bool Excute_DB(string _sql)
        {
            try
            {
                OpenConnect();
                _cmd = new OracleCommand(_sql);
                _cmd.Connection = _con;
                _cmd.ExecuteNonQuery();
                CloseConnect();
            }
            catch (System.Exception ex)
            {
                CloseConnect();
                _cl.UPDATE_LOG_FILE("ERR", null, "Excute_DB", "Excute_DB", ex.Message);
                return false;   
            }
            return true;
        }

        private string Delete_BySqlID(string sql)
        {
            OpenConnect();
            _cmd = new OracleCommand(sql);
            _cmd.Connection = _con;
            try
            {
                _cmd.ExecuteNonQuery();
                CloseConnect();
            }
            catch (System.Exception ex)
            {
                CloseConnect();
                _cl.UPDATE_LOG_FILE("ERR", null, "Delete_BySqlID", "Delete_BySqlID", ex.Message);
                return ex.Message;
            }
            return "OK";
        }

        public DataTable Execute_Store_By_ListString(string _storeName, DataTable _value)
        {
            try
            {
                OpenConnect();
                DataTable _result = new DataTable();
                _cmd.Connection = _con;
                _cmd.CommandType = CommandType.StoredProcedure;
                _cmd.CommandText = _storeName;
                foreach (DataColumn column in _value.Columns)
                {
                    string aa = _value.Rows[0][column].ToString();
                    string bb = _value.Rows[1][column].ToString();

                    OracleParameter op = new OracleParameter(column.ColumnName, _cl.RowType(_value.Rows[1][column].ToString()), 200);
                    if (bb == "D")
                    {
                        op.Value = DateTime.ParseExact(aa, "dd/MM/yyyy", CultureInfo.InvariantCulture);
                    }
                    else
                        op.Value = aa;
                    //op.Value = _value.Rows[0][column].ToString();
                    op.Direction = ParameterDirection.Input;
                    _cmd.Parameters.Add(op);

                }
                _cmd.Parameters.Add("PV_REFCURSOR", OracleType.Cursor).Direction = ParameterDirection.Output;
                _da.SelectCommand = _cmd;
                _da.Fill(_result);
                CloseConnect();
                return _result;

            }
            catch
            {
                OpenConnect();
                DataTable _result = new DataTable();
                _cmd = new OracleCommand();
                _cmd.Connection = _con;
                _cmd.CommandType = CommandType.StoredProcedure;
                _cmd.CommandText = _storeName;

                //-----------------------------------------
                _cmd.Parameters.Add("PV_REFCURSOR", OracleType.Cursor).Direction = ParameterDirection.Output;

                //-----------------------------------------
                OracleParameter op = new OracleParameter("OPT", OracleType.VarChar, 200);
                op.Value = "A";
                op.Direction = ParameterDirection.Input;
                _cmd.Parameters.Add(op);
                //-----------------------------------------
                op = new OracleParameter("BRID", OracleType.VarChar, 200);
                op.Value = "0001";
                op.Direction = ParameterDirection.Input;
                _cmd.Parameters.Add(op);

                foreach (DataColumn column in _value.Columns)
                {
                    string aa = _value.Rows[0][column].ToString();
                    string bb = _value.Rows[1][column].ToString();

                    op = new OracleParameter(column.ColumnName, _cl.RowType(_value.Rows[1][column].ToString()), 200);
                    if (bb == "D")
                    {
                        op.Value = DateTime.ParseExact(aa, "dd/MM/yyyy", CultureInfo.InvariantCulture);
                    }
                    else
                        op.Value = aa;
                    op.Direction = ParameterDirection.Input;
                    _cmd.Parameters.Add(op);
                }

                _da.SelectCommand = _cmd;
                _da.Fill(_result);
                CloseConnect();
                return _result;
            }
        }

        #endregion

        
        #region ham check thong tin
        
        public DataTable Return_Datatable(string _sql)
        {
            try
            {
                OpenConnect();
                _da = new OracleDataAdapter(_sql, _con);
                DataTable result = new DataTable("noname");
                _da.Fill(result);
                CloseConnect();
                return result;
            }
            catch(Exception ex){
                _cl.UPDATE_LOG_FILE("ERR",null,"Return_Datatable","Return_Datatable",ex.Message);
                return null;
            }
        }

        public bool Check_TrungGiaTri(string _value, string _tableName, string _columnName)
        {
            _cmd = new OracleCommand("select * from " + _tableName + " where " + _columnName + " like :param1", _con);
            _cmd.Parameters.Add("param1", OracleType.VarChar, 255).Value = _value;
            OpenConnect();
            DataTable result = new DataTable();
            _da.SelectCommand = _cmd;
            _da.Fill(result);
            if (result.Rows.Count > 0)
            {
                CloseConnect();
                return true;
            }
            CloseConnect();
            return false;
        }

        /// <summary>
        /// Tra ra ket qua tu cau truy van
        /// </summary>
        /// <param name="_sql"></param>
        /// <returns></returns>
        public DataTable SearchView_ByParameters(string _sql, DataTable _table)
        {
            DataTable result = new DataTable();
            try
            {
                OpenConnect();
                _cmd = new OracleCommand(_sql, _con);
                _cmd.Parameters.Clear();

                int i = 0;
                foreach (DataRow row in _table.Rows)
                {
                    string _columnName = Convert.ToString(row[0]);
                    string _pheptinh = Convert.ToString(row[1]);
                    string _value = Convert.ToString(row[2]);
                    string _datatype = Convert.ToString(row[3]);
                    switch (_pheptinh)
                    {
                        case "LIKE":
                            _cmd.CommandText += " and Upper(" + _columnName + ") " + _pheptinh + " '%' || :" + _columnName.Replace(".",i+"") + " || '%'";
                            break;
                        case "=":
                        case ">":
                        case "<":
                            if (_datatype=="D")
                            {
                                _cmd.CommandText += " and " + _columnName + " " + _pheptinh + " TO_DATE(:" + _columnName.Replace(".", i + "") + ",'dd/MM/yyyy')";    
                            }
                            else
                            {
                                _cmd.CommandText += " and " + _columnName + " " + _pheptinh + " :" + _columnName.Replace(".", i + "");
                            }
                            break;
                        default:
                            break;
                    }
                    _cmd.Parameters.AddWithValue(_columnName.Replace(".", i+""), _value);
                    i++;
                }
                _da.SelectCommand = _cmd;
                _da.Fill(result);
                CloseConnect();
                return result;
            }
            catch ( Exception ex)
            {
                CloseConnect();
                //_cl.CREATE_LOG_FILE("LOG");
                _cl.UPDATE_LOG_FILE("ERR", "", "SearchView_ByParameters","",ex.Message);
                return null;
            }
        }

        public DataTable SearchView_BySQL(string _sql)
        {
            OpenConnect();
            _da = new OracleDataAdapter(_sql, _con);
            DataTable result = new DataTable();
            _da.Fill(result);
            CloseConnect();
            return result;
        }

        public DataTable GetID_ByCombobox(string _sql, string _value)
        {
            DataTable result = new DataTable();
            try
            {
                OpenConnect();
                _cmd = new OracleCommand("select * from (" + _sql + ") where display like :param1", _con);
                _cmd.Parameters.Add("param1", OracleType.VarChar, 255).Value = _value;
                _da.SelectCommand = _cmd;
                _da.Fill(result);
                if (result.Rows.Count > 0)
                {
                    CloseConnect();
                    return result;
                }
                return result;
            }
            catch (Exception ex) { 
                CloseConnect();
                _cl.UPDATE_LOG_FILE("ERR", "", "GetID_ByCombobox", "GetID_ByCombobox", ex.Message);
                return result;
            }
        }

        public DataTable SearchView_BySearhCode(string v_strSearchCode, string _sql, string v_tlid)
        {
            try
            {
                OpenConnect();
                string cmd_sql = "Select searchcode, searchcmdsql From search where searchcode ='" + v_strSearchCode + "'";
                string cmd_sql_Main = "";
                _da = new OracleDataAdapter(cmd_sql, _con);
                DataTable result1 = new DataTable();
                _da.Fill(result1);
                if (result1.Rows.Count >= 1)
                {
                    cmd_sql_Main = result1.Rows[0]["searchcmdsql"].ToString();
                }

                string cmd_sqlField = "SELECT   fieldcode  FROM   searchfld WHERE display = 'Y' AND searchcode ='" + v_strSearchCode + "' order by position asc";
                _da = new OracleDataAdapter(cmd_sqlField, _con);
                DataTable result2 = new DataTable();
                _da.Fill(result2);

                int l = result2.Rows.Count;
                string v_strColumn = "";
                for (int i = 0; i < l; i++)
                {
                    v_strColumn += result2.Rows[i]["fieldcode"].ToString() + ",";
                }

                int k = v_strColumn.LastIndexOf(",");

                v_strColumn = v_strColumn.Substring(0, k);

                cmd_sql_Main = "Select " + v_strColumn + " From (" + cmd_sql_Main.Replace("<$TELLERID>", v_tlid) + ") where 0=0  " + _sql;

                _da = new OracleDataAdapter(cmd_sql_Main, _con);
                DataTable resultMain = new DataTable();
                _da.Fill(resultMain);
                result1.Clear();
                result2.Clear();
                CloseConnect();
                return resultMain;
            }
            catch (Exception ex)
            {
                _cl.UPDATE_LOG_FILE("ERR", v_tlid, "SearchView_BySearhCode", "SearchView_BySearhCode", ex.Message);
            }
            return null;
        }

        public string PrimaryKey(string _tablename)
        {
            DataTable _dt = this.Return_Datatable("select * from bsc_primary_key where table_name='" + _tablename + "' ");
            if (_dt.Rows.Count==0)
            {
                return null;
            }
            else
            {
                return _dt.Rows[0]["primarykey_name"].ToString();
            }
        }

        #endregion

        #region add, edit, delete

        public string AddNewRow(string _fldname, DataTable _tableValue)
        {
            // lấy toàn bộ cột trong bảng cần insert
            List<string> _para = new List<string>();
            List<object> _values = new List<object>();
            List<string> _datatype = new List<string>();
             
            // tạo câu lệnh insert dựa theo bảng _tableValue
            //string sql = "Insert into " + _fldname + " ("+this.PrimaryKey(_fldname)+",";
            string sql = "Insert into " + _fldname + " (";
            foreach (DataColumn column in _tableValue.Columns)
            {
                sql += column.ColumnName + ",";
                _para.Add(column.ColumnName);
                DataRow aa = _tableValue.Rows[0];
                _values.Add(_tableValue.Rows[0][column].ToString());
                _datatype.Add(_tableValue.Rows[1][column].ToString());
            }
            //sql = sql.Substring(0, sql.Length - 1) + ") values(scq_" + _fldname + ".NEXTVAL,";
            sql = sql.Substring(0, sql.Length - 1) + ") values(";
            foreach (DataColumn column in _tableValue.Columns)
            {
                sql += ":" + column.ColumnName + ",";
            }
            sql = sql.Substring(0, sql.Length - 1) + ")";

            // chạy lệnh insert, thành công trả lại True, lỗi trả lại messeage
                //WritetoTLLOG(_maker_ID, "", _tltxcd,_busdate,_txdesc,_txstatus,_msgacct,_chktime);
            return RunSql_ByParameter(sql, _para, _values, _datatype,"Add");
        }

        public string AddNewRowReturnID(string _fldname, DataTable _tableValue)
        {
            // lấy toàn bộ cột trong bảng cần insert
            List<string> _para = new List<string>();
            List<object> _values = new List<object>();
            List<string> _datatype = new List<string>();

            //lấy primarykey của bảng
            string primarykey = PrimaryKey(_fldname);

            // tạo câu lệnh insert dựa theo bảng _tableValue
            //string sql = "Insert into " + _fldname + " ("+this.PrimaryKey(_fldname)+",";
            string sql = "Insert into " + _fldname + " (";
            foreach (DataColumn column in _tableValue.Columns)
            {
                sql += column.ColumnName + ",";
                _para.Add(column.ColumnName);
                DataRow aa = _tableValue.Rows[0];
                _values.Add(_tableValue.Rows[0][column].ToString());
                _datatype.Add(_tableValue.Rows[1][column].ToString());
            }
            //sql = sql.Substring(0, sql.Length - 1) + ") values(scq_" + _fldname + ".NEXTVAL,";
            sql = sql.Substring(0, sql.Length - 1) + ") values(";
            foreach (DataColumn column in _tableValue.Columns)
            {
                sql += ":" + column.ColumnName + ",";
            }
            if (primarykey == null)
                sql = sql.Substring(0, sql.Length - 1) + ") returning autoid into :myOutputParameter";
            else
                sql = sql.Substring(0, sql.Length - 1) + ") returning "+ primarykey +" into :myOutputParameter";
            // chạy lệnh insert, thành công trả lại True, lỗi trả lại messeage
            //WritetoTLLOG(_maker_ID, "", _tltxcd,_busdate,_txdesc,_txstatus,_msgacct,_chktime);
            return RunSql_ByParameter_ReturnID(sql, _para, _values, _datatype, "Add");
        }

        public void WritetoTLLOG( string _nguoitao, string _nguoiduyet, string _magiaodich, string _ngaytao, string _ngayduyet,
            string _thoigiantao, string _thoigianduyet, string _duyet, string _tennghiepvu, string _sochungtu, string _motathem)
        {
            // Tao bang chua du lieu TLLOG
            DataTable tableTllog = new DataTable("BSC_TBL_TLLOG");
            
            // tao column
            tableTllog.Columns.Add("ngaytaogd");
            tableTllog.Columns.Add("ngayduyetgd");
            tableTllog.Columns.Add("magiaodich");
            tableTllog.Columns.Add("nguoitao");
            tableTllog.Columns.Add("nguoiduyet");
            tableTllog.Columns.Add("thoigiantao");
            tableTllog.Columns.Add("thoigianduyet");
            tableTllog.Columns.Add("duyet");
            tableTllog.Columns.Add("tennghiepvu");
            tableTllog.Columns.Add("sochungtu");
            tableTllog.Columns.Add("motathem");

            // tao row chua du lieu
            DataRow rowData = tableTllog.NewRow();
            rowData["ngaytaogd"] = _ngaytao;
            rowData["ngayduyetgd"] = _ngayduyet;
            rowData["nguoitao"] = _nguoitao;
            if (_nguoiduyet != "")
            {
                rowData["nguoiduyet"] = _nguoiduyet;
            }
            else
            {
                rowData["nguoiduyet"] = "";
            }
            rowData["magiaodich"] = _magiaodich;
            rowData["thoigiantao"] = _thoigiantao;
            rowData["thoigianduyet"] = _thoigianduyet;
            rowData["tennghiepvu"] = _tennghiepvu;
            rowData["sochungtu"] = _sochungtu;
            rowData["motathem"] = _motathem;
            rowData["duyet"] = _duyet;
          
            tableTllog.Rows.Add(rowData);

            // Tao row chua kieu du lieu
            DataRow rowDataType = tableTllog.NewRow();
            rowDataType["ngaytaogd"] = "T";
            rowDataType["ngayduyetgd"] = "T";
            rowDataType["magiaodich"] = "T";
            rowDataType["nguoitao"] = "T";
            rowDataType["nguoiduyet"] = "T";
            rowDataType["thoigiantao"] = "T";
            rowDataType["thoigianduyet"] = "T";
            rowDataType["duyet"] = "T";
            rowDataType["tennghiepvu"] = "T";
            rowDataType["sochungtu"] = "T";
            rowDataType["motathem"] = "T";
            tableTllog.Rows.Add(rowDataType);

            // cau lenh insert vao bang TLLOG
            //string sql = "Insert into BSC_TBL_TLLOG(ngaytaogd,ngayduyetgd,magiaodich,nguoitao,nguoiduyet,thoigiantao,thoigianduyet,duyet,tennghiepvu,sochungtu,motathem) values(" +
            //                ":ngaytaogd,:ngayduyetgd,:magiaodich,:nguoitao,:nguoiduyet,:thoigiantao,:thoigianduyet,:duyet,:tennghiepvu,:sochungtu,:motathem)";
            // tạo câu lệnh insert dựa theo bảng tableTllog
            string sql = "Insert into BSC_TBL_TLLOG (";
            foreach (DataColumn column in tableTllog.Columns)
            {
                sql += column.ColumnName + ",";
            }
            sql = sql.Substring(0, sql.Length - 1) + ") values(";
            foreach (DataColumn column in tableTllog.Columns)
            {
                sql += ":" + column.ColumnName + ",";
            }
            sql = sql.Substring(0, sql.Length - 1) + ")";

            //connect db
            OpenConnect();
            _cmd = new OracleCommand(sql);
            for (int i = 0; i < tableTllog.Columns.Count; i++)
            {
                _cmd.Parameters.Add(tableTllog.Columns[i].ToString(), _cl.RowType(rowDataType[i].ToString()), 255).Value = rowData[i].ToString();
            }
            _cmd.Connection = _con;
            try
            {
                _cmd.ExecuteNonQuery();
                CloseConnect();
            }
            catch (System.Exception ex)
            {
                CloseConnect();
                _cl.UPDATE_LOG_FILE("ERR", "", "WritetoTLLOG", "WritetoTLLOG", ex.Message);
            }
        }

        public string EditRow(string _fldname, DataTable _tableValue, string _ID_ColumnName, string _ID, string _UserID)
        {
            // lấy toàn bộ cột trong bảng cần insert
            List<string> _para = new List<string>();
            List<object> _values = new List<object>();
            List<string> _datatype = new List<string>();

            // tạo câu lệnh UPDATE dựa theo bảng vừa lấy
            string sql = "Update " + _fldname+ " SET ";
            foreach (DataColumn column in _tableValue.Columns)
            {
                // loại trừ trường ID, ko cần UPDATE
                if (column.ColumnName != _ID_ColumnName)
                {
                    sql += column.ColumnName + "=:" + column.ColumnName + ",";
                    _para.Add(column.ColumnName);
                    _values.Add(_tableValue.Rows[0][column].ToString());
                    _datatype.Add(_tableValue.Rows[1][column].ToString());
                }
            }
            //end parameter duyet

            sql = sql.Substring(0, sql.Length - 1) + " where " + _ID_ColumnName + "='" + _ID + "'";

            // chạy lệnh, thành công trả lại True, lỗi trả lại messeage
            //return WriteToMaintain_log(_fldname, _ID_ColumnName, _ID, _UserID);
            return RunSql_ByParameter(sql, _para, _values, _datatype, "Edit");
        }

        // Ghi ra maintain_log trước khi update 1 row
        public string WriteToMaintain_log(string _fldname, DataTable _tableValue,DataTable _OldTableValue, string _ID_ColumnName, string _ID, string _maker_ID)
        {
            try
            {
                // Truoc khi insert vao bsc_maintainLog, kiem tra xem bản ghi này đã đc sửa hay chưa, nếu chưa insert bt, nếu có ng sửa rồi thì xóa hết đi insert lại
                string _sqlwhere = _ID_ColumnName + "_" + _ID; ;
                DataTable result = this.Return_Datatable("select * from bsc_maintain_log where record_key='" + _sqlwhere + "' and approve_time is null");
                if (result.Rows.Count>0)
                {
                    this.Excute_DB("delete bsc_maintain_log where record_key='" + _sqlwhere + "' and approve_time is null");               
                }
                foreach (DataColumn column in _tableValue.Columns)
                {
                    // lay kieu du lieu, neu khac loai B (Image ) thi insert kieu khac
                    string _datatype = Convert.ToString(_tableValue.Rows[1][column]);
                    _cmd.Parameters.Add("table_name", OracleType.VarChar, 200).Value = _fldname;
                    _cmd.Parameters.Add("record_key", OracleType.VarChar, 200).Value = this.PrimaryKey(_fldname) + "_" + _ID;
                    _cmd.Parameters.Add("maker_id", OracleType.VarChar, 200).Value = _maker_ID;
                    _cmd.Parameters.Add("maker_dt", OracleType.DateTime, 200).Value = DateTime.Now;
                    _cmd.Parameters.Add("column_name", OracleType.VarChar, 200).Value = column.ColumnName;
                    _cmd.Parameters.Add("data_type", OracleType.VarChar, 100).Value = _tableValue.Rows[1][column].ToString();
                    if (_datatype == "B")
                    {
                        _cmd.CommandText = "insert into bsc_maintain_log (table_name,record_key, maker_id, maker_dt, column_name, temp_image, data_type) " +
                                    "values(:table_name,:record_key,:maker_id,:maker_dt,:column_name,:temp_image,:data_type)";
                        _cmd.Parameters.Add("temp_image", OracleType.VarChar).Value = _tableValue.Rows[0][column];
                    }
                    else
                    {
                        _cmd.CommandText = "insert into bsc_maintain_log (table_name,record_key, maker_id, maker_dt, column_name,from_value,to_value, data_type) " +
                                    "values(:table_name,:record_key,:maker_id,:maker_dt,:column_name,:from_value,:to_value,:data_type)";
                        if (_datatype == "D")
                        {
                            if (_OldTableValue.Rows[0][column.ColumnName].ToString().Length == 0)
                                _cmd.Parameters.Add("from_value", _cl.RowType(_datatype)).Value = OracleDateTime.Null;
                            else
                            {
                                _cmd.Parameters.Add("from_value", OracleType.VarChar).Value = _cm.Convert_String2DateTime(_OldTableValue.Rows[0][column.ColumnName].ToString(),"dd/MM/yyyy");
                            }

                            _cmd.Parameters.Add("to_value", OracleType.VarChar, 1000).Value = _tableValue.Rows[0][column].ToString();
                        }
                        else
                        {
                            _cmd.Parameters.Add("from_value", OracleType.VarChar, 1000).Value = _OldTableValue.Rows[0][column.ColumnName].ToString();
                            _cmd.Parameters.Add("to_value", OracleType.VarChar, 1000).Value = _tableValue.Rows[0][column].ToString();
                        }
                    }
                    _cmd.Connection = _con;
                    OpenConnect();
                    _cmd.ExecuteNonQuery();
                    _cmd.Parameters.Clear();
                }
                CloseConnect();
            }
            catch (Exception ex)
            {
                CloseConnect();
                _cl.UPDATE_LOG_FILE("ERR", _maker_ID, "WriteToMaintain_log", "WriteToMaintain_log", ex.Message);
                return ex.Message;
            }
            return CL_Ultility.messeseboxSuaThanhCong_CanDuyet;
        }

        public string DeleteRow(string _fldname, string _ID, string _ID_ColumnName, string _UserID)
        {
            string sql = "delete from " + _fldname + " where " + _ID_ColumnName + "='" + _ID + "'";
            // chạy lệnh, thành công trả lại True, lỗi trả lại messeage
            try
            {
                //WriteToLogData(_fldname, _ID_ColumnName, _ID, _UserID);
                string _rsl = Delete_BySqlID(sql);
                return _rsl;
            }
            catch (System.Exception ex)
            {
                _cl.UPDATE_LOG_FILE("ERR", _UserID, "DeleteRow", "DeleteRow", ex.Message);
                return ex.Message;
            }
        }

        //Phong tỏa trái phiếu
        public string Execute_Store(string _storeName, DataTable _value)
        {
            try
            {
                OpenConnect();
                DataTable _result = new DataTable();
                _cmd.Connection = _con;
                _cmd.CommandType = CommandType.StoredProcedure;
                _cmd.CommandText = _storeName;
                foreach (DataColumn column in _value.Columns)
                {
                    string aa = _value.Rows[0][column].ToString();
                    string bb = _value.Rows[1][column].ToString();

                    OracleParameter op = new OracleParameter(column.ColumnName, _cl.RowType(_value.Rows[1][column].ToString()), 200);
                    if (bb == "D")
                    {
                        op.Value = DateTime.ParseExact(aa, "dd/MM/yyyy", CultureInfo.InvariantCulture);
                    }
                    else
                        op.Value = aa;
                    
                    op.Direction = ParameterDirection.Input;
                    _cmd.Parameters.Add(op);

                    /*OracleParameter op = new OracleParameter(column.ColumnName, OracleType.VarChar, 200);
                    op.Value = _value.Rows[0][column].ToString();
                    op.Direction = ParameterDirection.Input;
                    _cmd.Parameters.Add(op);*/

                }
                //_cmd.Parameters.Add("result", OracleType.VarChar).Direction = ParameterDirection.Output;
                OracleParameter op1 = new OracleParameter("result", OracleType.VarChar, 200);
                op1.Value = "A";
                op1.Direction = ParameterDirection.Output;
                _cmd.Parameters.Add(op1);
                _cmd.ExecuteNonQuery();
                string rlt = _cmd.Parameters["result"].Value.ToString();
                CloseConnect();
                return rlt;
            }
            catch (Exception ex)
            {
                _cl.UPDATE_LOG_FILE("ERR", null, "Execute_Store", "Execute_Store", ex.Message);
                return ex.Message;
            }

        }

        //CheckApprove
        public int GetApproved(string _formName, string _ColumnName, string _id)
        {
            int check = -1;
            try
            {
                string sql = "select * from " + _formName + " where " + _ColumnName + " like '" + _id + "'";
                DataTable _tbl = new DataTable("approve");
                _tbl = Return_Datatable(sql);
                if (_tbl.Rows.Count > 0)
                {
                    if (_tbl.Columns.Contains("duyet"))
                        check = Convert.ToInt32(_tbl.Rows[0]["duyet"].ToString());
                    else
                        check = 1;
                }
                
                return check;
            }
            catch (Exception ex)
            {
                _cl.UPDATE_LOG_FILE("ERR", null, "GetApproved", "GetApproved", ex.Message);
                check = -1;
                return check;
            }
        }

        //update ten mau so TP
        public string UpdateTenMauSoTP(string _matp, string _filename)
        {
            try
            {
                OpenConnect();
                _cmd.Connection = _con;
                string sql = "update bsc_tbl_tpph set mausotc=:mausotc where matp=:matp ";
                _cmd.CommandText = sql;
                _cmd.Parameters.Add(":mausotc", OracleType.VarChar, 200).Value = _filename;
                _cmd.Parameters.Add(":matp", OracleType.VarChar, 200).Value = _matp;
                _cmd.ExecuteNonQuery();
                return "0";
            }
            catch (Exception ex)
            {
                _cl.UPDATE_LOG_FILE("ERR", null, "UpdateTenMauSoTP", "UpdateTenMauSoTP", ex.Message);
                return ex.Message;
            }
        }

        //Update du lieu that
        public string UpdateDataReal(string _fldname,string _ID_ColumnName, string _ID, string _UserID)
        {
            string sqltmp = _ID_ColumnName + "_" + _ID;
            string result = "";
            try
            {
                // lay du lieu temp tu maintain_log
                DataTable _datatmp = this.Return_Datatable("select * from bsc_maintain_log where record_key='" + sqltmp + "' and approve_time is null "
                                                            + "and ( (case when from_value is NULL then to_value "
                                                            + "when from_value <> to_value then to_value "
                                                            + "else null end ) is not null "
                                                            + "or temp_image is not null )");
                _datatmp.TableName = "bsc_maintain_log";
                //tao cau lenh update
                if (_datatmp.Rows.Count > 0)
                {
                    string _tableName = Convert.ToString(_datatmp.Rows[0]["table_name"]);
                    string _sql_update = "update " + _tableName + " set ";
                    List<string> _para = new List<string>();
                    List<object> _values = new List<object>();
                    List<string> _datatype = new List<string>();
                    foreach (DataRow row in _datatmp.Rows)
                    {
                        _sql_update += row["column_name"] + "=:" + row["column_name"] + ",";
                        _para.Add(Convert.ToString(row["column_name"]));
                        if (Convert.ToString(row["data_type"]) == "B")
                        {
                            _values.Add(row["temp_image"]);
                        }
                        else
                        {
                            _values.Add(row["to_value"]);
                        }
                        _datatype.Add(Convert.ToString(row["data_type"]));
                    }
                    _sql_update = _sql_update.Substring(0, _sql_update.Length - 1) + " where " + _ID_ColumnName + "='" + _ID + "'";
                    result = RunSql_ByParameter(_sql_update, _para, _values, _datatype, "Edit");
                    if (result != CL_Ultility.messeseboxSuaThanhCong)
                        return result;

                    // Sau khi update real xong, update truong approve_time trong maintainlog=ngay gio hien tai
                    this.Excute_DB("update bsc_maintain_log set approve_time='" + DateTime.Now.ToString() + "', " +
                                                               "approve_id ='" + _UserID + "' " +
                                                                "where record_key='" + sqltmp + "' and approve_time is null");
                }
            }
            catch (Exception ex)
            {
                _cl.UPDATE_LOG_FILE("ERR", null, "UpdateDataReal", "UpdateDataReal", ex.Message);
            }
            return result;
        }

        #endregion
    }
}
